﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows;

namespace SimpleLibrary
{
    /// <summary>
    /// Database driver to connect and get data from oledb database. In this case, Access database.
    /// </summary>
    class DbDriver
    {
        //Private members
        private OleDbConnection _connection;
        
        //Singelton class as DbDriver is used for Conection purpose.
        private static DbDriver _instance = null;
        public static DbDriver Instance
        {
            private set
            {
                _instance = value;
            }
            get
            {
                if (_instance == null)
                    _instance = new DbDriver();
                return _instance;
            }
        }
        
        public void Init()
        {
            
        }

        private DbDriver()
        {
            //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Z:\Work\Library\SimpleLibrary\Library.accdb

            _connection = new OleDbConnection();
            _connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\Library.accdb";

            try
            {
                _connection.Open();
            }
            catch (Exception exception)
            {
                //rethrow the exception to indicate Database not available
                MessageBox.Show("Not able to connect to DB. Make sure it is placed along with EXE in the same path",
                                "DB Error");
            }
        }
        #region select Queries

        public DataTable Getusers(ref Int64 userCount)
        {
            DataTable table = new DataTable("Users");
            String queryString = "SELECT     ID, strName, strPhoneNo, strAddress, strUserName, strMailId FROM tblUsers";
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(queryString,_connection);
                if(_connection.State != ConnectionState.Open)
                    _connection.Open();

                userCount = adapter.Fill(table);
            }
            catch
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return table;
        }

        public DataTable GetBooks(ref Int64 bookCount)
        {
            DataTable table = new DataTable("Books");
            string queryString = "SELECT     ID, strTitle, strAuthor, strYear, strPublisher, strPhotoPath FROM tblBooks";
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                bookCount = adapter.Fill(table);
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return table;
        }

        public List<Int32> GetpendingBooks(String userID)
        {
            String queryString =
                "SELECT     tblBooks.ID FROM  ((tblLendingList INNER JOIN tblUsers ON tblLendingList.nUserId = tblUsers.ID) INNER JOIN tblBooks ON tblLendingList.nBookId = tblBooks.ID) WHERE     (tblLendingList.bReturned = 0) AND (tblUsers.ID = " +
                userID + ")";
            List<Int32> pendingBooks = new List<Int32>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString,_connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                if(reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        pendingBooks.Add(Int32.Parse(reader["ID"].ToString()));    
                    }
                }
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return pendingBooks;
        }

        public List<Int64> GetReturnedBooks(String userName)
        {
            String queryString =
                "SELECT     tblBooks.ID FROM  ((tblLendingList INNER JOIN tblUsers ON tblLendingList.nUserId = tblUsers.ID) INNER JOIN tblBooks ON tblLendingList.nBookId = tblBooks.ID) WHERE     (tblLendingList.bReturned = true) AND (tblUsers.strUserName = '" +
                userName + "')";
            List<Int64> returnedBooks = new List<long>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        returnedBooks.Add(reader.GetInt64(0));
                    }
                   
                }
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return returnedBooks;
        }

        public List<Int64> GetPreviousLenders(String bookName)
        {
            String queryString =
                "SELECT     tblUsers.ID FROM         ((tblLendingList INNER JOIN tblUsers ON tblLendingList.nUserId = tblUsers.ID) INNER JOIN tblBooks ON tblLendingList.nBookId = tblBooks.ID) WHERE     (tblLendingList.bReturned = true) AND (tblBooks.strTitle = '" +
                bookName + "')";

            List<Int64> previousOwners = new List<long>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        previousOwners.Add(reader.GetInt64(0));    
                    }
                }
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return previousOwners;
        }

        public Int64 GetPresentStatus(String bookId)
        {
            String queryString =
                "SELECT     tblUsers.ID FROM         ((tblLendingList INNER JOIN tblUsers ON tblLendingList.nUserId = tblUsers.ID) INNER JOIN tblBooks ON tblLendingList.nBookId = tblBooks.ID) WHERE     (tblLendingList.bReturned = false) AND (tblBooks.ID = " +
                bookId + ")";
            try
            {
                Int64 userId = 0;
                OleDbCommand command = new OleDbCommand(queryString,_connection);
                if(_connection.State != ConnectionState.Open)
                    _connection.Open();
                object returnValue = command.ExecuteScalar();

                if (ReferenceEquals(null, returnValue) || returnValue.GetType().Equals(typeof(DBNull)))
                    return -1;
                userId = (Int32) returnValue;
                return userId;
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return -1;
        }
        #endregion

        #region update Queries

        public bool UpdateUserDetails(String userId, String phoneNo,String address, String mailId )
        {
            return true;
        }

        public bool ReturnBook(String id)
        {
            String queryString =
                "UPDATE       tblLendingList SET bReturned = true WHERE        (tblLendingList.nBookId = " + id + ")";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                int resultAffected = command.ExecuteNonQuery();
                if (resultAffected > 0)
                    return true;
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return false;
        }

        #endregion

        #region Insert Queries

        public Int64 InsertNewUser(String userName,String name,String phoneNo,String address,String mailId)
        {
            string queryString = "INSERT INTO tblUsers  (strName, strPhoneNo, strAddress, strMailId) VALUES        ('" +
                                 name + "', '" + phoneNo + "', '" + address + "', '" + mailId + "')";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                Object resultAffected = command.ExecuteNonQuery();

                return Int64.Parse(resultAffected.ToString());
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
           
        }

        public Int64 InsertNewBook(String title,String athour, String publisher,String year)
        {
            String queryString = "INSERT INTO tblBooks (strTitle, strAuthor, strYear, strPublisher,strPhotoPath) VALUES        ('" +
                                 title + "', '" + athour + "', '" + year + "', '" + publisher + "', '" + String.Empty + "')";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                Object resultAffected = command.ExecuteNonQuery();

                return Int64.Parse(resultAffected.ToString());
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            
        }

        public bool LendBook(string bookId,string userId, Int16 duration)
        {
            String queryString = "INSERT INTO tblLendingList (nUserId, nBookId, nDuration) VALUES        (" + userId +
                                 ", " + bookId + ", " + duration + ")";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString,_connection);
                if(_connection.State != ConnectionState.Open)
                    _connection.Open();

                int resultAffected = command.ExecuteNonQuery();
                if (resultAffected == 1)
                    return true;
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return false;
        }


        #endregion

        public bool IsValidUser(string userID)
        {
            string queryString =
                "SELECT        COUNT(tblLendingList.nBookId) AS Expr1 FROM (tblLendingList INNER JOIN tblUsers ON tblLendingList.nUserId = tblUsers.ID) WHERE        (tblLendingList.bReturned = false) AND (tblUsers.ID = " +
                userID + ")";
            bool validUser = true;
            try
            {
               
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                object returnValue = command.ExecuteScalar();

                if (ReferenceEquals(null, returnValue) || returnValue.GetType().Equals(typeof(DBNull)))
                    return false;
                if (Int32.Parse(returnValue.ToString()) >= 2)
                    validUser = false;

                queryString =
                    "SELECT Count(tblLendingList.ID) AS Expr1 FROM tblLendingList WHERE (((([tblLendingList].[dtDateOfLending])+[nDuration])<Now()) AND (tblLendingList.bReturned = false) AND (tblLendingList.nUserId)=" +
                    userID + ")";
                command = new OleDbCommand(queryString,_connection);
                returnValue = command.ExecuteScalar();

                if (ReferenceEquals(null, returnValue) || returnValue.GetType().Equals(typeof(DBNull)))
                    return false;
                if (Int32.Parse(returnValue.ToString()) > 0)
                {
                    throw new BadHistoryUser();
                }

            }
            catch{}
            return validUser;
        }

        internal bool IsBookAvailable(string bookId)
        {
            string queryString =
                "SELECT        COUNT(ID) AS Expr1 FROM            tblLendingList WHERE        (bReturned = false) HAVING        (nBookId = " +
                bookId + ")";
            try
            {

                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                object returnValue = command.ExecuteScalar();

                if (ReferenceEquals(null, returnValue) || returnValue.GetType().Equals(typeof(DBNull)))
                    return false;
                if (Int32.Parse(returnValue.ToString()) > 0)
                    return false;
            }
            catch
            {
                return false;
            }
            return true;
        }

        internal bool DeleteUser(string userId)
        {
            string queryString = "DELETE FROM tblUsers  WHERE        (tblUsers.ID = " + userId + ")";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                int resultAffected = command.ExecuteNonQuery();
                if (resultAffected == 1)
                    return true;
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return false;
        }

        internal bool DeleteBook(string bookId)
        {
            string queryString = "DELETE FROM tblBooks WHERE        (tblBooks.ID = " + bookId + ")";
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();

                int resultAffected = command.ExecuteNonQuery();
                if (resultAffected == 1)
                    return true;
            }
            catch (Exception)
            {
                //rethrow the exception to indicate Database not available
                throw new Exception("Database is not available. Do not mess with me.");
            }
            return false;
        }

        internal List<string> GetAllAvailableBooks()
        {
            String queryString =
                "SELECT        strTitle FROM            tblBooks";
            List<String> availableBooks = new List<String>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        availableBooks.Add(reader["strTitle"].ToString());
                    }
                }
            }
            catch (Exception exception)
            {
                Logger.WriteError("Error in getting data",exception.ToString());
            }
            return availableBooks;
        }

        internal List<string> GetAllBorrowedBooks()
        {
            String queryString =
                "SELECT DISTINCT        tblBooks.strTitle FROM            (tblLendingList INNER JOIN  tblBooks ON tblLendingList.nBookId = tblBooks.ID) WHERE        (tblLendingList.bReturned = false)";
            List<String> availableBooks = new List<String>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        availableBooks.Add(reader["strTitle"].ToString());
                    }
                }
            }
            catch (Exception)
            {
            }
            return availableBooks;
        }

        internal List<string> GetAllExpiredRentals()
        {
            String queryString =
                "SELECT DISTINCT        tblUsers.strName FROM            (tblLendingList INNER JOIN  tblUsers ON tblLendingList.nUserId = tblUsers.ID) WHERE        (tblLendingList.bReturned = false) AND (tblLendingList.dtDateOfLending + tblLendingList.nDuration < NOW()) GROUP BY tblUsers.strName";

            List<String> expiredRental = new List<String>();
            try
            {
                OleDbCommand command = new OleDbCommand(queryString, _connection);
                if (_connection.State != ConnectionState.Open)
                    _connection.Open();
                OleDbDataReader reader = command.ExecuteReader();
                if (reader != null && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        expiredRental.Add(reader["strName"].ToString());
                    }
                }
            }
            catch (Exception)
            {
            }
            return expiredRental;
        }
    }
}
